package cn.com.infcn.util;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.ArrayUtils;

/**
 * 数据库查询助手 (需要用到 Common-DBUtils 包)
 * 
 * @author 谢鹏<br>
 */

@SuppressWarnings("unchecked")
public class QueryHelper {
	// jdbc支持 getPreparedStatementMetaData 时使用
	// private final static QueryRunner _g_runner = new QueryRunner();

	private final static QueryRunner _g_runner = new QueryRunner(true); // jdbc不支持
																		// getPreparedStatementMetaData
																		// 时使用
	@SuppressWarnings("rawtypes")
	private final static ColumnListHandler _g_columnListHandler = new ColumnListHandler() {
		@Override
		protected Object handleRow(ResultSet rs) throws SQLException {
			Object obj = super.handleRow(rs);
			if (obj instanceof BigInteger)
				return ((BigInteger) obj).longValue();
			return obj;
		}

	};
	@SuppressWarnings("rawtypes")
	private final static ScalarHandler _g_scaleHandler = new ScalarHandler() {
		@Override
		public Object handle(ResultSet rs) throws SQLException {
			Object obj = super.handle(rs);
			if (obj instanceof BigInteger)
				return ((BigInteger) obj).longValue();
			return obj;
		}
	};

	@SuppressWarnings("serial")
	private final static List<Class<?>> PrimitiveClasses = new ArrayList<Class<?>>() {
		{
			add(Long.class);
			add(Integer.class);
			add(String.class);
			add(java.util.Date.class);
			add(java.sql.Date.class);
			add(java.sql.Timestamp.class);
		}
	};

	private final static boolean _IsPrimitive(Class<?> cls) {
		return cls.isPrimitive() || PrimitiveClasses.contains(cls);
	}

	/**
	 * 读取某个对象
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public static <T> T read(Connection conn, Class<T> beanClass, String sql, Object... params) {
		try {
			return (T) _g_runner.query(conn, sql,
					_IsPrimitive(beanClass) ? _g_scaleHandler : new BeanHandler(beanClass), params);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 对象查询
	 * 
	 * @param <T>
	 * @param beanClass
	 * @param sql
	 * @param params
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public static <T> List<T> query(Connection conn, Class<T> beanClass, String sql, Object... params) {
		try {
			return (List<T>) _g_runner.query(conn, sql,
					_IsPrimitive(beanClass) ? _g_columnListHandler : new BeanListHandler(beanClass), params);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 分页查询(MySQL版)
	 * 
	 * @param <T>
	 * @param beanClass
	 * @param sql
	 * @param page
	 * @param count
	 * @param params
	 * @return
	 */
	public static <T> List<T> query_sliceMySQL(Connection conn, Class<T> beanClass, String sql, int page, int count,
			Object... params) {
		if (page < 0 || count < 0)
			throw new IllegalArgumentException("Illegal parameter of 'page' or 'count', Must be positive.");
		int from = (page - 1) * count;
		count = (count > 0) ? count : Integer.MAX_VALUE;
		return query(conn, beanClass, sql + " LIMIT ?,?", ArrayUtils.addAll(params, new Integer[] { from, count }));
	}

	/**
	 * 
	 * 描述: 分页查询(MySQL版)
	 *
	 * @param conn
	 * @param sql
	 * @param page
	 * @param count
	 * @param params
	 * @return List<Map<String,Object>>(即table)
	 */
	public static List<Map<String, Object>> query_sliceMySQL_Map(Connection conn, String sql, int page, int count,
			Object... params) {
		if (page < 0 || count < 0)
			throw new IllegalArgumentException("Illegal parameter of 'page' or 'count', Must be positive.");
		int from = (page - 1) * count;
		count = (count > 0) ? count : Integer.MAX_VALUE;

		sql = sql + " LIMIT ? , ? ";
		return query_Map(conn, sql, ArrayUtils.addAll(params, new Integer[] { from, count }));
	}

	/**
	 * 
	 * 描述:返回map的查询
	 *
	 * @param conn
	 * @param sql
	 * @param params
	 * @return List<Map<String,Object>>
	 */
	@SuppressWarnings("rawtypes")
	public static List<Map<String, Object>> query_Map(Connection conn, String sql, Object... params) {
		try {
			List<Map<String, Object>> temp = (List<Map<String, Object>>) _g_runner.query(conn, sql,
					new MapListHandler(), params);
			return temp;

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	@SuppressWarnings("rawtypes")
	public static List<Map<String, Object>> query_Map_Mysql(Connection conn, String sql, Object... params) {
		try {
			List<Map<String, Object>> temp = (List<Map<String, Object>>) _g_runner.query(conn, sql,
					new MapListHandler(), params);
			return temp;

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 分页查询(Oracle版)
	 * 
	 * @param <T>
	 * @param beanClass
	 * @param sql
	 * @param page
	 * @param count
	 * @param params
	 * @return
	 */
	public static <T> List<T> query_sliceOracle(Connection conn, Class<T> beanClass, String sql, int startRow,
			int endRow, Object... params) {
		if (startRow < 0 || endRow < 0)
			throw new IllegalArgumentException("Illegal parameter of 'startRow' or 'endRow', Must be positive.");

		sql = "SELECT * FROM (SELECT ROWNUM RN, res.* FROM (" + sql + ") res WHERE ROWNUM <=?) WHERE RN >=?";

		startRow = (startRow > 0) ? startRow : Integer.MAX_VALUE;
		endRow = (endRow > 0) ? endRow : Integer.MAX_VALUE;

		return query(conn, beanClass, sql, ArrayUtils.addAll(params, new Integer[] { endRow, startRow }));
	}

	/**
	 * 
	 * 描述: 分页查询(MySQL版)
	 *
	 * @param conn
	 * @param sql
	 * @param page
	 * @param count
	 * @param params
	 * @return List<Map<String,Object>>(即table)
	 */
	public static List<Map<String, Object>> query_sliceOracle_Map(Connection conn, String sql, int page, int count,
			Object... params) {

		int start = (page - 1) * count + 1;
		int end = page * count;

		if (page < 0 || count < 0)
			throw new IllegalArgumentException("Illegal parameter of 'startRow' or 'endRow', Must be positive.");

		sql = "SELECT * FROM (SELECT ROWNUM RN, res.* FROM (" + sql + ") res WHERE ROWNUM <=?) WHERE RN >=?";

		page = (page > 0) ? page : Integer.MAX_VALUE;
		count = (count > 0) ? count : Integer.MAX_VALUE;
		if (params.length > 0) {
			return query_Map(conn, sql, ArrayUtils.addAll(params, new Integer[] { end, start }));
		} else {
			return query_Map(conn, sql, new Integer[] { end, start });
		}

	}

	/**
	 * 分页查询(Adapter版)
	 * 
	 * @param <T>
	 * @param beanClass
	 * @param sql
	 * @param page
	 * @param count
	 * @param params
	 * @return
	 */
	public static <T> List<T> query_slice(Connection conn, Class<T> beanClass, String sql, int condition1,
			int condition2, Object... params) {
		return query_sliceOracle(conn, beanClass, sql, condition1, condition2, params);
	}

	/**
	 * 执行统计查询语句，语句的执行结果必须只返回一个数值
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static long count(Connection conn, String sql, Object... params) {
		try {
			Number num = (Number) _g_runner.query(conn, sql, _g_scaleHandler, params);
			return (num != null) ? num.longValue() : -1;
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}

	/**
	 * 执行INSERT/UPDATE/DELETE语句
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int update(Connection conn, String sql, Object... params) {
		try {
			return _g_runner.update(conn, sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}

	/**
	 * 批量执行指定的SQL语句
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int[] batch(Connection conn, String sql, Object[][] params) {
		try {
			return _g_runner.batch(conn, sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

}